#!/bin/env python
# -*- coding: utf-8 -*-
# Filename:    main.py
# Revision:    1.0
# Date:        2012-06-14
# Author:      simonzhang
# web:         www.simonzhang.net
# Email:       simon-zzm@163.com
### END INIT INFO
import torndb
from config import *

try:
    db = torndb.Connection(db_ip, db_database, db_user, db_pass, db_conn_time)
except (AttributeError, MySQLdb.OperationalError):
    db = torndb.Connection(db_ip, db_database, db_user, db_pass, db_conn_time)


def get_user_count(user_name):
    _get = db.query("select count(*) as c from user where user =\'%s\'" % user_name)
    return _get

def db_get_passwd(user_name):
    for projects in db.query("SELECT user,passwd from user where user='%s'" % user_name):
        return projects.passwd

def get_left_menu_list(menu_list):
    _get = db.query("select id,menu_name,link from left_menu where id in (%s)" % menu_list)
    return _get

def get_left_menu_list_all():
    _get = db.query("select id,menu_name,link from left_menu")
    return _get

def get_project_list_all():
    _get = db.query("select id,default_reboot,project,default_version_management from project")
    return _get


def get_global_set():
    _get = db.query("select title,domainname from global_set")
    return _get

def get_left_menu_id(user_name):
    _get = db.query("select manage_menu from user where user=\'%s\'" % user_name)
    return _get

def get_manage_project(user_name):
    _get = db.query("select manage_project from user where user=\'%s\'" % user_name)
    return _get

def get_user_project_list(project_id):
    if len(project_id) >0:
        _get = db.query("select id, project, default_reboot, default_version_management \
                          from project \
                          where id in (%s)" % project_id)
    else:
        _get = ''
    return _get

def get_project_package_name(project_num):
    _get = db.query("SELECT id,project_name,create_data,deploy_status,deploy_log_name,mark \
                     FROM project_history \
                     WHERE project_name=\'%s\' ORDER BY id desc" % project_num)
    return _get

def odb_project_id(project_name):
    try:
        _get = db.query("SELECT id FROM project WHERE project = \'%s\'" % project_name)[0]['id']
    except:
        _get = '0'
    return _get

def odb_upload_deploy_lock(_project_name):
    try:
        _get = db.query("SELECT upload_lock,upload_lock_time, \
                                deploy_lock,deploy_lock_time \
                         FROM project \
                         WHERE project = '%s'" % (_project_name))[0]
    except:
        _get='ERROR'
    return _get

def idb_insert_new_package_name(package_name, createtime, \
                                deploy_status, package_from, mark):
    try:
        _get_mark = "%s" %  mark
        db.execute("INSERT INTO project_history \
                  SET project_name='%s', \
                      create_data='%s', \
                      deploy_status='%s', \
                      package_from='%s', \
                      mark='%s'" % (package_name, \
                                     createtime, \
                                     deploy_status, \
                                     package_from, \
                                     _get_mark))
        _get='OK'
    except:
        _get='ERROR'
    return _get

def odb_version_server_info(project_name):
    try:
        _get = db.query('select svn_url, git_url from project where project=\'%s\'' % (project_name))[0]
    except:
        _get = 'ERROR'
    return _get

def idb_make_package_lock(_project_name, _time):
        db.execute("UPDATE project \
                    SET upload_lock = 1, \
                        upload_lock_time = '%s' \
                    WHERE project = '%s'"% (_time, _project_name))
        _get='OK'

def idb_make_package_unlock(_project_name, _time):
    try:
        db.execute("UPDATE project \
                    SET upload_lock = 0, \
                        upload_lock_time = '%s' \
                    WHERE project = '%s'"% (_time, _project_name))
        _get='OK'
    except:
        _get='ERROR'
    return _get


def idb_deploy_lock(_project_name, _time):
    try:
        db.execute("UPDATE project \
                    SET deploy_lock = 1, \
                        deploy_lock_time = '%s' \
                    WHERE project = '%s'"% (_time, _project_name))
        _get='OK'
    except:
        _get='ERROR'
    return _get

def idb_deploy_unlock(_project_name, _time):
    try:
        db.execute("UPDATE project \
                    SET deploy_lock = 0, \
                        deploy_lock_time = '%s' \
                    WHERE project = '%s'"% (_time, _project_name))
        _get='OK'
    except:
        _get='ERROR'
    return _get

def idb_deploy_id_unlock(_project_id, _time):
    try:
        db.execute("UPDATE project \
                    SET deploy_lock = 0, \
                        deploy_lock_time = '%s' \
                    WHERE id = '%s'"% (_time, _project_id))
        _get='OK'
    except:
        _get='ERROR'
    return _get

def odb_project_log_name(package, date):
    try:
        _get = db.query("SELECT deploy_log_name \
                         FROM project_history \
                         WHERE project_name='%s' \
                               and create_data='%s'" % (package, date))[0]['deploy_log_name']
    except:
        _get = 'ERROR'
    return _get

def idb_project_log_name(package, date, log_name, deploy_status):
    try:
        db.execute("UPDATE project_history \
                    SET deploy_log_name='%s', \
                        deploy_status = '%s' \
                    WHERE project_name='%s' \
                        and create_data='%s'" % (log_name, deploy_status, \
                                                 package, date))
        _get = 'OK'
    except:
        _get = 'ERROR'
    return _get


def odb_project_back_status(package, date):
    try:
        _get = db.query("SELECT deploy_status \
                         FROM project_history \
                         WHERE project_name='%s' \
                               and create_data='%s'" % \
                               (package, date))[0]['deploy_status']
    except:
        _get = 'ERROR'
    return _get

def odb_project_service_list(_project_id):
    try:
        _get = db.query("SELECT * FROM project_info WHERE projectid = '%s'" % _project_id)
    except:
        _get = 'ERROR'
    return _get

def idb_deploy_result(_project_id, _project_service_name, _deploy_package, _status):
    try:
        db.execute("UPDATE project_info \
                    SET deploy_package='%s', deploy_info = '%s' \
                    WHERE projectid = '%s' and servername ='%s'" % \
                         (_deploy_package, _status, _project_id, _project_service_name))
        _get='OK'
    except:
        _get='ERROR'
    return _get


def add_project(project_name, remark):
    try:
        _project_name = "%s" % project_name
        _remark = "%s" % remark
        db.query('INSERT INTO project SET project=\'%s\',remark=\'%s\'' % (_project_name, _remark))
        _get='OK'
    except:
        _get='ERROR'
    return _get

def odb_project_info(project_name):
    try:
        _get = db.query("select default_reboot, \
                                svn_url, \
                                svn_username, \
                                svn_password, \
                                git_url, \
                                git_username, \
                                git_password, \
                                default_version_management, \
                                thread, \
                                thread_num, \
                                wait_time, \
                                upload_lock, \
                                deploy_lock, \
                                remark \
                                from project where project='%s'" % project_name)[0]
    except:
        _get = 'ERROR'
    return _get

def idb_insert_project_default(project_name, default_reboot_value, get_default_thread_value, \
                               get_default_wait_time, get_default_thread_num):
    try:
        db.query("update project set default_reboot = '%s', thread = '%s', \
                                wait_time = '%s', thread_num ='%s' \
                                where project='%s'" % (default_reboot_value, get_default_thread_value, \
                                                       get_default_wait_time, \
                                                       get_default_thread_num, project_name))
        _get = 'OK'
    except:
        _get = 'ERROR'
    return _get

def idb_svn(project_name, svnusername, svnpasswd, svnurl):
    try:
        db.execute("update project set svn_url = '%s', \
                                  svn_username = '%s', \
                                  svn_password = '%s' \
                                  where project='%s'" % (svnurl, \
                                                         svnusername, \
                                                         svnpasswd, \
                                                         project_name))
        _get = 'OK'
    except:
        _get = 'ERROR'
    return _get

def idb_git(project_name, gitusername, gitpasswd, giturl):
    try:
        db.execute("update project set git_url = '%s', \
                                     git_username = '%s', \
                                     git_password = '%s' \
                                     where project='%s'" % (giturl, \
                                                            gitusername, \
                                                            gitpasswd, \
                                                            project_name))
        _get = 'OK'
    except:
        _get = 'ERROR'
    return _get

def idb_version_manage_default(_ver_man_default, project_name):
    try:
        db.execute("update project \
                    set default_version_management = '%s' \
                    where project = '%s'" % (_ver_man_default, \
                                             project_name))
        _get = 'OK'
    except:
        _get = 'ERROR'
    return _get

def odb_projcet_server_count(_server_name, _project_id):
    try:
        _get = db.query("SELECT count(*) as c \
                         FROM project_info \
                         WHERE servername = \'%s\' and projectid = \'%s\'" % (_server_name, \
                                                                              _project_id))
        _get = _get[0]['c']
    except:
        _get=-1
    return _get

def idb_project_server_insert(_server_name, _project_id, _host, _port, \
                              _user, _password, _remote_path, \
                              _reboot_comm, _shutdown, \
                              _startup, _checkurl):
    try:
        db.execute('INSERT INTO project_info SET servername=\'%s\', \
                   projectid=\'%s\', ip=\'%s\', \
                   port=\'%s\', user=\'%s\', passwd=\'%s\', \
                   remotepath=\'%s\', reboot=\'%s\', \
                   shutdown=\'%s\', startup=\'%s\', \
                   checkurl=\'%s\' ' % (_server_name, _project_id, \
                              _host, _port, \
                              _user, _password, _remote_path, \
                              _reboot_comm, _shutdown, \
                              _startup, _checkurl)) 
        _get='OK'                                    
    except:
        _get='ERROR'
    return _get

def idb_project_server_update(_server_name, _project_id, _host, _port, \
                              _user, _password, _remote_path, \
                              _reboot_comm, _shutdown, \
                              _startup, _checkurl):
    try:
        db.execute('UPDATE project_info SET ip=\'%s\', \
                   port=\'%s\', user=\'%s\', passwd=\'%s\', \
                   remotepath=\'%s\', reboot=\'%s\', \
                   shutdown=\'%s\', startup=\'%s\', \
                   checkurl=\'%s\' WHERE servername=\'%s\' and \
                   projectid=\'%s\'' % (_host, _port, \
                              _user, _password, _remote_path, \
                              _reboot_comm, _shutdown, \
                              _startup, _checkurl, _server_name, _project_id))
        _get='OK' 
    except:
        _get='ERROR'
    return _get

def idb_version_config_update(_project_id, _svn_url, _svn_user, _svn_passwd, \
                              _git_url, _git_user, _git_passwd):
    try:
        db.execute("UPDATE project SET svn_url='%s', \
                   svn_username='%s', svn_password='%s', git_url='%s', \
                   git_username='%s', git_password='%s' \
                   WHERE id='%s'" % (_svn_url, _svn_user, _svn_passwd, \
                              _git_url, _git_user, _git_passwd, _project_id))
        _get='OK'
    except:
        _get='ERROR'
    return _get


def odb_project_server_list(_project_id):
    try:
        _get = db.query('SELECT * FROM project_info WHERE projectid = %s' % _project_id)
    except:
        _get = 'ERROR'
    return _get

def odb_version_config_list(_project_id):
    try:
        _get = db.query('SELECT * FROM project WHERE id = %s' % _project_id)
    except:
        _get = 'ERROR'
    return _get

def odb_after_deploy_do(_project_id):
    try:
        _get = db.query('SELECT * FROM project WHERE id = %s' % _project_id)
    except:
        _get = 'ERROR'
    return _get

def idb_after_deploy_do(_defaul_treboot, _wait_time, _thread, _threadnum, _remark):
    try:
        db.execute("UPDATE project SET default_reboot='%s', \
                   wait_time='%s', thread='%s', thread_num='%s', \
                   remark='%s'" % (_defaul_treboot, _wait_time, \
                                   _thread, _threadnum, _remark))
        _get='OK'
    except:
        _get='ERROR'
    return _get


def idb_del_service(service_id):
    try:
        db.execute('delete from project_info where id=\'%s\'' % service_id)
        _get='OK'
    except:
        _get='ERROR'
    return _get
def odb_get_new_project_package(project_name):
    try:
        _get = db.query("SELECT project_name,create_data,deploy_log_name,deploy_log_name \
                          FROM project_history \
                          WHERE project_name = '%s' \
                          ORDER BY create_data DESC LIMIT 1" % project_name)[0]
    except:
        _get = 'ERROR'
    return _get

def get_all_user_name():
    _get = db.query('select user from user')
    return _get

def insert_global_webinfo(domain, title):
    try:
        db.query('update global_set SET domainname=\'%s\',title=\'%s\' WHERE id=0' % (domain, title))
        _get='OK'
    except:
        _get='ERROR'
    return _get


def get_db_user_info(user):
    try:
        _get = db.query('select id from user where user=\'%s\'' % (user))[0]
    except:
        _get = ''
    return _get

def idb_init_user_passwd(user, passwd):
    try:
        db.execute('UPDATE user SET passwd=\'%s\' where user=\'%s\'' % (passwd, user))
        _get='OK'
    except:
        _get='ERROR'
    return _get

def add_user_info(user, passwd, email):
    try:
        _info_help_id = db.query('select id from left_menu where link =\'info_help\'')[0]['id']
        db.query('INSERT INTO user SET user=\'%s\', \
                                       passwd=\'%s\', \
                                       email=\'%s\', \
                                       manage_menu=\'%s\', \
                                       manage_project=\'0\'' % (user, \
                                                                passwd, \
                                                                email, \
                                                                _info_help_id))
        _get='OK'
    except:
        _get='ERROR'
    return _get

def add_project_conf(project_name, status):
    try:
        db.query('UPDATE project SET conf_status=\'%s\' WHERE project=\'%s\'' % (status, project_name))
        _get='OK'
    except:
        _get='ERROR'
    return _get

def get_user_auth_id(user_name):
    _get = db.query('SELECT manage_project,manage_menu FROM user where user=\'%s\'' % user_name)
    return _get


def edit_user_auth(user_name, project_id, menu_id):
    try:
        db.query('UPDATE user SET manage_project=\'%s\',manage_menu=\'%s\' WHERE user = \'%s\'' % (project_id, menu_id, user_name))
        _get='OK'
    except:
        _get='ERROR'
    return _get
   

def add_check_job(project_name, job_name, check_url): 
    try:
        db.query('INSERT INTO check_deploy SET project_name=\'%s\',\
                                               check_job_name=\'%s\',\
                                               check_url=\'%s\',\
                                               check_status=\'1\'' % (project_name,\
                                                                      job_name, check_url))
        _get='OK'
    except:
        _get='ERROR'
    return _get


def get_project_check_job(project_name):
    _get = db.query('SELECT service_name,check_url FROM check_deploy WHERE project_name=\'%s\'' % project_name)
    return _get



def idb_del_package_history(package_name, package_date):
    try:
        db.execute('DELETE FROM project_history \
                    WHERE project_name=\'%s\' \
                          and create_data=\'%s\''%\
                          (package_name, package_date))
        _get = 'OK'
    except:
        _get='ERROR'
    return _get

def odb_deploy_log_list(package_name, package_date):
    try:
        _get = db.query('SELECT deploy_log_name \
                         FROM project_history \
                         WHERE project_name=\'%s\' \
                               and create_data=\'%s\''%\
                               (package_name, package_date))[0]['deploy_log_name']
    except:
        _get='ERROR'
    return _get


def odb_project_new_info(project_name):
    try:
        _get = db.query("SELECT create_data, mark, deploy_status \
                         FROM project_history \
                         WHERE project_name='%s' \
                         ORDER BY id desc limit 1" % project_name)
    except:
        _get='ERROR'
    return _get

def odb_project_url_info(project_id):
    try:
        _get = db.query("SELECT servername,`shutdown`, \
                                startup,reboot,checkurl \
                         FROM project_info \
                         WHERE projectid='%s'"% project_id)
    except:
        _get='ERROR'
    return _get

def idb_project_url_status(_status, _project_id, _service_name):
    try:
        db.execute("UPDATE project_info \
                    SET checkurl_info='%s' \
                    WHERE projectid='%s' \
                        and servername='%s'" % \
                          (_status, _project_id, _service_name))
        _get = 'OK'
    except:
        _get = "ERROR"
    return _get

def odb_project_deploy_info(project_id, filter_deploy, filter_service):
    try:
        term = "projectid = %s " % project_id
        if filter_deploy != 'undefined':
            term += "and deploy_info !=0 "
        if filter_service != 'undefined':
            term += "and checkurl_info = 1"
        _get = db.query("SELECT servername, checkurl_info, \
	                        deploy_package, deploy_info \
                         FROM project_info \
                         WHERE %s" % term)
    except:
        _get=0
    return _get

def db_get_edit_user_passwd_md5(user):
    try:
        _get = db.query("SELECT passwd FROM user WHERE user=\'%s\'" % user)[0]["passwd"]
    except:
        _get='ERROR'
    return _get


def db_edit_user_passwd(user, passwd, email):
    try:
        db.query('UPDATE user SET passwd=\'%s\', email=\'%s\' where user=\'%s\'' % (passwd, email, user))
        _get='OK'
    except:
        _get='ERROR'
    return _get

def odb_get_service_info(project_id, service_name):
    try:
        _get = db.query("SELECT passwd \
                         FROM project_info \
                         WHERE projectid='%s' AND servername='%s'" %(project_id, \
                                                                     service_name))
    except:
        _get='ERROR'
    return _get

def odb_get_last_date():
    try:
        _get = db.query("SELECT lastdate FROM version")
    except:
        _get = 'ERROR'
    return _get
